﻿-- (名称表)
create table tree
(
	tree_no bigint primary key identity(1,1) not null,
	tree_name varchar(60) unique,	--树名称(学名)
	latin_name	varchar(60),--拉丁名
	class_name_1 varchar(60),--统称
	class_name_2 varchar(60),--统称
	class_name_3 varchar(60),--统称
	conv_name_1	varchar(60),--俗名
	conv_name_2	varchar(60),--俗名
	conv_name_3	varchar(60),--俗名
	conv_name_4	varchar(60),--俗名
	conv_name_5	varchar(60),--俗名
	conv_name_6	varchar(60),--俗名
	conv_name_7	varchar(60),--俗名
	conv_name_8	varchar(60),--俗名
	conv_name_9	varchar(60),--俗名
	conv_name_10	varchar(60),--俗名
	conv_name_11	varchar(60),--俗名
	conv_name_12	varchar(60),--俗名
	conv_name_13	varchar(60),--俗名
	conv_name_14	varchar(60),--俗名
	conv_name_15	varchar(60),--俗名,
	description varchar(MAX),	--描述,
	imagelink varchar(60) default('')
)
alter table tree add class_name as ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'')
alter table tree add conv_name as isnull(conv_name_1,'')+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'')

--(属性类型表，颜色/数值/列表等)
create table tree_attr_type 
(
	type_no	varchar(20) primary key	not null,--属性类型编号
	type_name varchar(20), --属性类型名称
	description varchar(60) default('')
)

--(属性表，包括颜色)
create table tree_attr
(
	attr_no	varchar(20) not null,--属性编号
	attr_value varchar(60)not null,	--属性值
	type_no varchar(20) not null foreign key references tree_attr_type(type_no),--属性类型
	pub bit not null default(0),	--大众版
	pro bit not null default(1),	--专业版
	description varchar(60) default(''),
	imagelink varchar(60) default('')
	
	constraint PK_TREE_ATTR primary key clustered(attr_no,type_no)
)

--(树表)
create table tree_concept
(
	concept_id bigint not null identity(1,1) primary key,
	tree_no bigint not null foreign key references tree(tree_no),
	shuxing1 varchar(20),
	shuxing2 varchar(20),
	yexu1 varchar(20),
	yexu2 varchar(20),
	yexing1 varchar(20),
	yexing2 varchar(20),
	yexing3 varchar(20),
	yexing4 varchar(20),
	yexing5 varchar(20),
	yeyuan1 varchar(20),
	yeyuan2 varchar(20),
	yejian1 varchar(20),
	yejian2 varchar(20),
	yeji1 varchar(20),
	yeji2 varchar(20),
	yeji3 varchar(20),
	yeji4 varchar(20),
	yeji5 varchar(20),
	huaxing1 varchar(20),
	huaxing2 varchar(20),
	guoshi1 varchar(20),
	guoshi2 varchar(20),
	shupi1 varchar(20),
	shupi2 varchar(20),
	danfuye varchar(20),
	guoshiyanse varchar(60),
	huaqi  varchar(60),
	gaodu varchar(60),
	shuganyanse varchar(60)
)

alter table tree_concept add shuxing3 varchar(20)

create table tree_concept_ext
(
	concept_ext_id bigint not null identity(1,1) primary key,
	tree_no bigint not null foreign key references tree(tree_no),
	type_no  varchar(20) foreign key references tree_attr_type(type_no),
	attr_value varchar(100),
)

--(树的归属)
create table tree_category
(
	category_id bigint identity(1,1) not null primary key,
	tree_no bigint not null foreign key references tree(tree_no),	
	sibling_tree_name varchar(100),--同属物
	symbiosis varchar(100),--共生植物
	genus_id varchar(100),		--属
	family_id varchar(100),		--科
	class_id varchar(100),		--纲
	phylum_id varchar(100),		--门
)

create table tree_use
(
	use_id bigint identity(1,1) not null primary key,
	tree_no bigint not null foreign key references tree(tree_no),
	--(树的功用)
	use_garden varchar(MAX),	--园林用途	
	use_med varchar(MAX),		--药用价值	
	use_eco varchar(MAX),		--经济用途	
	use_air	varchar(MAX),	--改善空气	
	use_other varchar(MAX),	--其他
	--(树的生态特征)
	eco_lifecycle varchar(MAX),	--生长过程	
	eco_environment varchar(MAX),	--生长环境	
	eco_habit varchar(MAX),		--生长习性
	--(树的种植养护)
	plant_propagate varchar(MAX),		--繁殖方法	
	plant_water varchar(MAX),			--浇水	
	plant_fertilize varchar(MAX),		--施肥	
	plant_illness_pro varchar(MAX),		--常见病防治	
	plant_insect_pro varchar(MAX),		--病虫害防治
	--(树的人文价值)
	culture_symbol varchar(MAX),		--象征意义	
	culture_legend varchar(MAX),		--神话传说	
	culture_classic varchar(MAX),		--重要典故
)

---外部链接
create table tree_link
(
	tree_no bigint not null foreign key references tree(tree_no),		
	link_name varchar(60),			--链接名称，百度等
	link_url varchar(255)			--链接url
	constraint PK_TREE_LINK primary key clustered(tree_no,link_name,link_url)
)

--(树的个性变异)
create table tree_derive
(
	derive_no bigint not null primary key identity(1,1),
	tree_name varchar(60) unique,	--树名称(学名)
	parent_tree_no bigint not null foreign key references tree(tree_no),
	description varchar(MAX) default(''),	--描述
)

--(树的个性变异)
create table tree_derive_concept
(
	concept_id bigint not null identity(1,1) primary key,
	derive_no bigint not null foreign key references tree_derive(derive_no),
	type_no  varchar(20) foreign key references tree_attr_type(type_no),
	attr_value varchar(100),
)

--我的收集
create table tree_gather
(
	gather_id bigint primary key not null identity(1,1),
	uid int not null,--user id from dnt system
	
	image_link varchar(100) not null,
	site varchar(100) not null,
	title varchar(512) not null,
	created datetime default(getdate()) not null,
	description varchar(1024) default('')
)
go

--(名称反向试图)
create view tree_pivot as
select class_name_1 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names
from tree
where class_name_1 is not null
union all
select class_name_2 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where class_name_2 is not null
union all
select class_name_3 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where class_name_3 is not null
union all
select conv_name_1 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where conv_name_1 is not null
union all
select conv_name_2 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where conv_name_2 is not null
union all
select conv_name_3 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+' '+isnull(conv_name_2,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where conv_name_3 is not null
union all
select conv_name_4 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+' '+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where conv_name_4 is not null
union all
select conv_name_5 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+' '+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where conv_name_5 is not null
union all
select conv_name_6 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+' '+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where conv_name_6 is not null
union all
select conv_name_7 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+' '+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where conv_name_7 is not null
union all
select conv_name_8 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+' '+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where conv_name_8 is not null
union all
select conv_name_9 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+' '+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where conv_name_9 is not null
union all
select conv_name_10 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+' '+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where conv_name_10 is not null
union all
select conv_name_11 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+' '+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where conv_name_11 is not null
union all
select conv_name_12 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+' '+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where conv_name_12 is not null
union all
select conv_name_13 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+' '+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_14,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where conv_name_13 is not null
union all
select conv_name_14 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+' '+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_15,'') as eq_conv_names 
from tree
where conv_name_14 is not null
union all
select conv_name_15 conv_name, 
		tree_no,tree_name,latin_name,ISNULL(class_name_1,'')+' '+ISNULL(class_name_2,'')+' '+ISNULL(class_name_3,'') class_names,
		isnull(conv_name_1,'')+' '+isnull(conv_name_2,'')+' '+isnull(conv_name_3,'')+' '+isnull(conv_name_4,'')+' '+isnull(conv_name_5,'')+' '+isnull(conv_name_6,'')+' '+isnull(conv_name_7,'')+' '+isnull(conv_name_8,'')+' '+isnull(conv_name_9,'')+' '+isnull(conv_name_10,'')+' '+isnull(conv_name_11,'')+' '+isnull(conv_name_12,'')+' '+isnull(conv_name_13,'')+' '+isnull(conv_name_14,'') as eq_conv_names 
from tree
where conv_name_15 is not null
go

--system tables
create table sys_setting
(
	setting_id bigint primary key not null identity(1,1),
	name varchar(60),
	value varchar(255)
)
go

create table sys_book
(
	book_id bigint primary key not null identity(1,1),
	name varchar(255),
	author varchar(255),
	publisher varchar(255),
	pub_date datetime,
	isbn  varchar(20),
	description  varchar(1024),
	cover_image  varchar(255),
	book_link1  varchar(255),
	book_link2  varchar(255),
	book_link3  varchar(255)
)
go